package sisi.enquires;

import java.util.Date;
import java.util.List;

import javax.persistence.EntityManager;

/**
 * ReqshipfuelassController.java - Controller class for the <i>reqshipfuelass database table</i>. 
 * @author Pedro Gonzalez pedro@sisisoftware.it
 * @author Claudio Pelosi claudio@sisisoftware.it
 * @author SISI Software S.n.c. - www.sisisoftware.it
 * @version 1.0, 09/1/2014
 * 
 */

public class ReqshipfuelassController{

	private EntityManager getEntityManager() {
		return new sisi.MyEmf().getEntityManager();
	}

	public Reqshipfuelass[] get(String cerca) {
		List<Reqshipfuelass> lista = getList(cerca);
			return lista.toArray(new Reqshipfuelass[0]);
		}

	@SuppressWarnings("unchecked")
	public List<Reqshipfuelass> getList(String cerca) {

		String cWhere = "";
		if (cerca != null && !cerca.isEmpty()) {
			cWhere = " WHERE TRIM( BOTH FROM UPPER(c.vesselname) ) LIKE :cerca ";
		}

		EntityManager em = getEntityManager();
		try {
			javax.persistence.Query q = em
				.createQuery("select c from Reqshipfuelass as c " + cWhere 
					+ " order by c.id DESC ");

			if (cerca != null && !cerca.isEmpty()) {
				q.setParameter("cerca", '%'+cerca.trim().toUpperCase()+'%' ); 
			}

			List<Reqshipfuelass> lista = q.getResultList();
			return lista;
		} finally {
			em.close();
		}
	}
	
	@SuppressWarnings("unchecked")
	public List<Reqshipfuelass> getList(Integer vesselnameid, String vesselname, int fromIndex, int qta) {

		EntityManager em = getEntityManager();
		try {
			String cString = "select c from Reqshipfuelass as c ";
			
			if (vesselnameid!=null || ( vesselname!=null && !vesselname.isEmpty()) ){
				cString += " WHERE ";
			}
			
			if (vesselnameid != null){
				cString += " c.vesselnameid = "+vesselnameid; 
			}
			
			if (vesselname!=null && !vesselname.isEmpty()){
				if (vesselnameid!=null){
					cString += " AND ";
				}
				cString += " UPPER( c.vesselname ) LIKE ?1 ";
			}

			cString += " order by c.id DESC ";
			
			javax.persistence.Query q = em
				.createQuery(cString);

			if (vesselname!=null && !vesselname.isEmpty()){
				q.setParameter(1, "%"+vesselname.trim().toUpperCase()+"%");
			}
			q.setFirstResult(fromIndex);
			q.setMaxResults(qta);


			List<Reqshipfuelass> lista = q.getResultList();
			return lista;
		} finally {
			em.close();
		}
	}


	public int getCount(Integer vesselnameid, String vesselname) {

		EntityManager em = getEntityManager();
		try {
			String cString = "select count(c) from Reqshipfuelass c ";
			
			if (vesselnameid!=null || (vesselname!=null && !vesselname.isEmpty()) ){
				cString += " WHERE ";
			}
			
			if (vesselnameid != null){
				cString += " c.vesselnameid = "+vesselnameid; 
			}
			
			if (vesselname!=null && !vesselname.isEmpty()){
				if (vesselnameid!=null){
					cString += " AND ";
				}
				cString += " UPPER( c.vesselname ) LIKE ?1 ";
			}
			
			javax.persistence.Query q = em
					.createQuery(cString);
			
			if (vesselname!=null && !vesselname.isEmpty()){
				q.setParameter(1, "%"+vesselname.trim().toUpperCase()+"%");
			}
			
			Long result = ((Long) q.getSingleResult());
			int quantirecords = result.intValue();
			return quantirecords;
		} finally {
			em.close();
		}
	}
	
	
	public int lastIdPlusOne() {

		EntityManager em = getEntityManager();
		try {

			String cString = "select max(c.id) from Reqshipfuelass c ";

			javax.persistence.Query q = em.createQuery(cString);

			Object risultato = q.getSingleResult();
			String valore;
			if (risultato != null) {
				valore = (q.getSingleResult().toString());
			} else {
				valore = "0";
			}
			int lastid = Integer.parseInt(valore);

			return (lastid+1);
		} catch (Exception e) {
			// se c'e un errore, mandiamo sempre valore uno, come codice.
			return Integer.parseInt("1");
		} finally {
			em.close();
		}
	}
	
	
	public Reqshipfuelass getxId(int id) {

		EntityManager em = getEntityManager();
		try {
			Reqshipfuelass reqshipfuelassx = em.find(Reqshipfuelass.class, id);
			return reqshipfuelassx;
		} finally {
			em.close();
		}
	}

	@SuppressWarnings("finally")
	public Reqshipfuelass add(Reqshipfuelass reqshipfuelass) {
		EntityManager em = getEntityManager();
		try {
			em.getTransaction().begin();
			em.persist(reqshipfuelass);
			em.getTransaction().commit();
			reqshipfuelass = refresh(reqshipfuelass);
		} finally {
			em.close();
			return reqshipfuelass;
		}
	}

	@SuppressWarnings("finally")
	public boolean update(Reqshipfuelass reqshipfuelass) {
		EntityManager em = getEntityManager();
		try {
			em.getTransaction().begin();
			Reqshipfuelass reqshipfuelassx = em.find(Reqshipfuelass.class, reqshipfuelass.getId());
			reqshipfuelassx.setLocation(reqshipfuelass.getLocation());
			reqshipfuelassx.setId(reqshipfuelass.getId());
			reqshipfuelassx.setStatus(reqshipfuelass.getStatus());
			reqshipfuelassx.setNote(reqshipfuelass.getNote());
			reqshipfuelassx.setAgent(reqshipfuelass.getAgent());
			reqshipfuelassx.setAgentid(reqshipfuelass.getAgentid());
			reqshipfuelassx.setArt15(reqshipfuelass.getArt15());
			reqshipfuelassx.setBert(reqshipfuelass.getBert());
			reqshipfuelassx.setCaptain(reqshipfuelass.getCaptain());
			reqshipfuelassx.setDeliverydt(reqshipfuelass.getDeliverydt());
			reqshipfuelassx.setDeliverytime(reqshipfuelass.getDeliverytime());
			reqshipfuelassx.setDtup(reqshipfuelass.getDtup());
			reqshipfuelassx.setEtadt(reqshipfuelass.getEtadt());
			reqshipfuelassx.setEtatime(reqshipfuelass.getEtatime());
			reqshipfuelassx.setEtddt(reqshipfuelass.getEtddt());
			reqshipfuelassx.setEtdtime(reqshipfuelass.getEtdtime());
			reqshipfuelassx.setExchrate(reqshipfuelass.getExchrate());
			reqshipfuelassx.setFuel(reqshipfuelass.getFuel());
			reqshipfuelassx.setFuelconn(reqshipfuelass.getFuelconn());
			reqshipfuelassx.setIdlastport(reqshipfuelass.getIdlastport());
			reqshipfuelassx.setIdnextport(reqshipfuelass.getIdnextport());
			reqshipfuelassx.setIdportref(reqshipfuelass.getIdportref());
			reqshipfuelassx.setLastport(reqshipfuelass.getLastport());
			reqshipfuelassx.setLocphone(reqshipfuelass.getLocphone());
			reqshipfuelassx.setNextport(reqshipfuelass.getNextport());
			reqshipfuelassx.setNotea(reqshipfuelass.getNotea());
			reqshipfuelassx.setNotes(reqshipfuelass.getNotes());
			reqshipfuelassx.setOil(reqshipfuelass.getOil());
			reqshipfuelassx.setOth(reqshipfuelass.getOth());
			reqshipfuelassx.setPaidc(reqshipfuelass.getPaidc());
			reqshipfuelassx.setPaids(reqshipfuelass.getPaids());
			reqshipfuelassx.setPobox(reqshipfuelass.getPobox());
			reqshipfuelassx.setPorttoref(reqshipfuelass.getPorttoref());
			reqshipfuelassx.setRefused(reqshipfuelass.getRefused());
			reqshipfuelassx.setSatcom(reqshipfuelass.getSatcom());
			reqshipfuelassx.setSpare(reqshipfuelass.getSpare());
			reqshipfuelassx.setVesselname(reqshipfuelass.getVesselname());
			reqshipfuelassx.setVesselnameid(reqshipfuelass.getVesselnameid());
			em.getTransaction().commit();
			em.flush();

		} finally {
			em.close();
		return false;
		}
	}

@SuppressWarnings("finally")
public Reqshipfuelass refresh(Reqshipfuelass reqshipfuelass) {

		Reqshipfuelass reqshipfuelassx = reqshipfuelass;
		EntityManager em = getEntityManager();
		try {
			em.getTransaction().begin();
			reqshipfuelassx = em.find(Reqshipfuelass.class, reqshipfuelass.getId());
			em.flush();
			em.refresh(reqshipfuelassx);
		} finally {
			em.close();
			return reqshipfuelassx;
		}
}

@SuppressWarnings("finally")
public boolean remove(Reqshipfuelass reqshipfuelass) {
		EntityManager em = getEntityManager();
		try {
			em.getTransaction().begin();
			Reqshipfuelass reqshipfuelassx = em.find(Reqshipfuelass.class, reqshipfuelass.getId());
			em.remove(reqshipfuelassx);
			em.getTransaction().commit();
		} finally {
			em.close();
			return false;
		}
}



@SuppressWarnings("rawtypes")
public List getListaIdRichiesteAperteUnique(Integer idYacht) {

	EntityManager em = getEntityManager();
	try {
		String cQuery = "";
		cQuery += "select distinct ON (reqshipfuelassdetail.id) reqshipfuelassdetail.id, ";
		cQuery += 		" reqshipfuelassdetail.seq, reqshipfuelassdetail.productname, " +
						" reqshipfuelassdetail.productid, reqshipfuelassdetail.quantity, " +
						" reqshipfuelassdetail.um, reqshipfuelassdetail.id as reqid, " +
						" reqshipfuelassdetail.stat, status.description, reqshipfuelass.vesselname, " +
						" reqshipfuelass.porttoref, reqshipfuelassdetail.note, reqshipfuelassdetail.id, " +
						" reqquote.suppid, suppliers.companyname, reqshipfuelassdetail.idmaster "+ 
						" from (((reqshipfuelass left join status as status_1 on reqshipfuelass.status = status_1.status) " + 
						" right join (reqshipfuelassdetail " + 
						" left join status on reqshipfuelassdetail.stat = status.status) on reqshipfuelass.id = reqshipfuelassdetail.id) " + 
						" left join reqquote on (reqshipfuelassdetail.id = reqquote.reqid) and (reqshipfuelassdetail.seq = reqquote.reqseq)) " + 
						" left join suppliers on reqquote.suppid = suppliers.supplid " +
						" where (((reqshipfuelassdetail.stat)='SE' or (reqshipfuelassdetail.stat)='IQ') AND reqshipfuelass.vesselnameid = "+idYacht + " ) "+ 
						" order by reqshipfuelassdetail.id DESC, reqshipfuelassdetail.seq ";
	
		//System.out.println(cQuery);
		
		javax.persistence.Query q = em
				.createNativeQuery(cQuery); 

		List val = q.getResultList();
		return val;
	} finally {
		em.close();
	}
}


@SuppressWarnings("rawtypes")
public List getListaIdRichiesteAperteDettaglio(Integer idEnqNumber) {

	EntityManager em = getEntityManager();
	try {
		String cQuery = "select reqshipfuelassdetail.id, ";		
		cQuery += 		" reqshipfuelassdetail.seq, reqshipfuelassdetail.productname, " +
						" reqshipfuelassdetail.productid, reqshipfuelassdetail.quantity, " +
						" reqshipfuelassdetail.um, reqshipfuelassdetail.id as reqid, " +
						" reqshipfuelassdetail.stat, status.description, reqshipfuelass.vesselname, " +
						" reqshipfuelass.porttoref, reqshipfuelassdetail.note, reqshipfuelassdetail.id, " +
						" reqquote.suppid, suppliers.companyname, reqshipfuelassdetail.idmaster "+ 
						" from (((reqshipfuelass left join status as status_1 on reqshipfuelass.status = status_1.status) " + 
						" right join (reqshipfuelassdetail " + 
						" left join status on reqshipfuelassdetail.stat = status.status) on reqshipfuelass.id = reqshipfuelassdetail.id) " + 
						" left join reqquote on (reqshipfuelassdetail.id = reqquote.reqid) and (reqshipfuelassdetail.seq = reqquote.reqseq)) " + 
						" left join suppliers on reqquote.suppid = suppliers.supplid " +
						" where (((reqshipfuelassdetail.stat)='SE' or (reqshipfuelassdetail.stat)='IQ') AND reqshipfuelassdetail.id = "+idEnqNumber + " ) "+ 
						" order by reqshipfuelassdetail.id, reqshipfuelassdetail.seq ";
	
		
		//System.out.println(cQuery);
		
		javax.persistence.Query q = em
				.createNativeQuery(cQuery);

		List val = q.getResultList();
		return val;
	} finally {
		em.close();
	}
}


@SuppressWarnings("rawtypes")
public List getListaEnqNumberIQ_QC() {

	EntityManager em = getEntityManager();
	try {
		String cQuery = "";
		
		
		cQuery += "select reqshipfuelassdetail.id, reqshipfuelass.deliverydt, reqshipfuelass.porttoref, reqshipfuelassdetail.stat, "+ 
					" anayacht.id, anayacht.name, anayacht.corporation, reqshipfuelass.idportref, anayacht.captain, ports.country, " + 
					" reqshipfuelass.agentid, reqshipfuelass.agent, (CASE reqshipfuelass.art15 WHEN true THEN 'yes' ELSE 'no' END) as art15x  "+
					" from ((anayacht inner join reqshipfuelass on anayacht.id = reqshipfuelass.vesselnameid)  "+
					" inner join ports on reqshipfuelass.idportref = ports.id)  "+
					" left join (suppliers right join reqshipfuelassdetail on suppliers.supplid = reqshipfuelassdetail.supplierid) on reqshipfuelass.id = reqshipfuelassdetail.id "+
					" group by reqshipfuelassdetail.id, reqshipfuelass.deliverydt, reqshipfuelass.porttoref, reqshipfuelassdetail.stat, "+
					" anayacht.id, anayacht.name, anayacht.corporation, reqshipfuelass.idportref, anayacht.captain, ports.country,  "+
					" reqshipfuelass.agentid, reqshipfuelass.agent, (CASE reqshipfuelass.art15 WHEN true THEN 'yes' ELSE 'no' END) "+
					" having (((reqshipfuelassdetail.id)>1000) and ((reqshipfuelassdetail.stat)='IQ' or (reqshipfuelassdetail.stat)='QC')) "+
					" order by reqshipfuelassdetail.id desc;  ";
		
		javax.persistence.Query q = em
				.createNativeQuery(cQuery);

		List val = q.getResultList();
		return val;
	} finally {
		em.close();
	}
}


@SuppressWarnings("rawtypes")
public List getListaEnqNumberQC_SC_CC_HI() {

	EntityManager em = getEntityManager();
	try {
		String cQuery = "";
		
		
		cQuery += "select reqshipfuelassdetail.id, reqshipfuelass.deliverydt, reqshipfuelass.porttoref, reqshipfuelassdetail.stat, "+ 
					" anayacht.id, anayacht.name, anayacht.corporation, reqshipfuelass.idportref, anayacht.captain, ports.country, " + 
					" reqshipfuelass.agentid, reqshipfuelass.agent, (CASE reqshipfuelass.art15 WHEN true THEN 'yes' ELSE 'no' END) as art15x  "+
					" from ((anayacht inner join reqshipfuelass on anayacht.id = reqshipfuelass.vesselnameid)  "+
					" inner join ports on reqshipfuelass.idportref = ports.id)  "+
					" left join (suppliers right join reqshipfuelassdetail on suppliers.supplid = reqshipfuelassdetail.supplierid) on reqshipfuelass.id = reqshipfuelassdetail.id "+
					" group by reqshipfuelassdetail.id, reqshipfuelass.deliverydt, reqshipfuelass.porttoref, reqshipfuelassdetail.stat, "+
					" anayacht.id, anayacht.name, anayacht.corporation, reqshipfuelass.idportref, anayacht.captain, ports.country,  "+
					" reqshipfuelass.agentid, reqshipfuelass.agent, (CASE reqshipfuelass.art15 WHEN true THEN 'yes' ELSE 'no' END) "+
					" having (((reqshipfuelassdetail.id)>1000) and ((reqshipfuelassdetail.stat)='QC' or (reqshipfuelassdetail.stat)='SC' or (reqshipfuelassdetail.stat)='CC' or (reqshipfuelassdetail.stat)='HI') ) "+
					" order by reqshipfuelassdetail.id desc;  ";
		
		javax.persistence.Query q = em
				.createNativeQuery(cQuery);

		List val = q.getResultList();
		return val;
	} finally {
		em.close();
	}
}



@SuppressWarnings("rawtypes")
public List getListaEnqNumberCC_SC_HI_CL_IV() {

	EntityManager em = getEntityManager();
	try {
		String cQuery = "";
		
		
		cQuery += "select reqshipfuelassdetail.id, reqshipfuelass.deliverydt, reqshipfuelass.porttoref, reqshipfuelassdetail.stat, "+ 
					" anayacht.id, anayacht.name, anayacht.corporation, reqshipfuelass.idportref, anayacht.captain, ports.country, " + 
					" reqshipfuelass.agentid, reqshipfuelass.agent, (CASE reqshipfuelass.art15 WHEN true THEN 'yes' ELSE 'no' END) as art15x  "+
					" from ((anayacht inner join reqshipfuelass on anayacht.id = reqshipfuelass.vesselnameid)  "+
					" inner join ports on reqshipfuelass.idportref = ports.id)  "+
					" left join (suppliers right join reqshipfuelassdetail on suppliers.supplid = reqshipfuelassdetail.supplierid) on reqshipfuelass.id = reqshipfuelassdetail.id "+
					" group by reqshipfuelassdetail.id, reqshipfuelass.deliverydt, reqshipfuelass.porttoref, reqshipfuelassdetail.stat, "+
					" anayacht.id, anayacht.name, anayacht.corporation, reqshipfuelass.idportref, anayacht.captain, ports.country,  "+
					" reqshipfuelass.agentid, reqshipfuelass.agent, (CASE reqshipfuelass.art15 WHEN true THEN 'yes' ELSE 'no' END) "+
					" having (((reqshipfuelassdetail.id)>1000) and ((reqshipfuelassdetail.stat)='CC' or (reqshipfuelassdetail.stat)='SC' or (reqshipfuelassdetail.stat)='HI' or (reqshipfuelassdetail.stat)='CL' or (reqshipfuelassdetail.stat)='IV' )) "+
					" order by reqshipfuelassdetail.id DESC ;  "; 
		
		javax.persistence.Query q = em
				.createNativeQuery(cQuery);

		List val = q.getResultList();
		return val;
	} finally {
		em.close();
	}
}


@SuppressWarnings("rawtypes")
public List getListaEnqNumberCC_SC_HI_CL_IV_Supplier() {

	EntityManager em = getEntityManager();
	try {
		String cQuery = "";
		
		
		cQuery += "select reqshipfuelassdetail.id as order, anayacht.id, anayacht.name, "+
				 " reqshipfuelass.deliverydt, anayacht.captain, reqshipfuelass.porttoref, "+
				 " anayacht.corporation, reqshipfuelassdetail.curr, "+
				 " suppinvoicedetail.suppid, suppinvoicedetail.suppname, reqshipfuelassdetail.id::text || suppinvoicedetail.suppid::text "+
				 " as key, reqshipfuelass.art15 from ((captains right join (anayacht right "+
				 " join reqshipfuelass on anayacht.id = reqshipfuelass.vesselnameid) on "+
				 " captains.contactname = anayacht.captain) right join reqshipfuelassdetail "+
				 " on reqshipfuelass.id = reqshipfuelassdetail.id) left join "+
				 " suppinvoicedetail on (reqshipfuelassdetail.productid = "+
				 " suppinvoicedetail.productid) and (reqshipfuelassdetail.id = "+
				 " suppinvoicedetail.order_) where (((reqshipfuelassdetail.stat)='CC' or "+
				 " (reqshipfuelassdetail.stat)='SC' or (reqshipfuelassdetail.stat)='HI' or "+
				 " (reqshipfuelassdetail.stat)='CL' or (reqshipfuelassdetail.stat)='IV')) "+
				 " group by reqshipfuelassdetail.id, anayacht.id, anayacht.name, "+
				 " reqshipfuelass.deliverydt, anayacht.captain, reqshipfuelass.porttoref, "+
				 " anayacht.corporation, reqshipfuelassdetail.curr, "+
				 " suppinvoicedetail.suppid, suppinvoicedetail.suppname, "+
				 " reqshipfuelass.art15 having (((reqshipfuelassdetail.id)>1000)) order by "+
				 " reqshipfuelassdetail.id DESC "; 
		
		javax.persistence.Query q = em
				.createNativeQuery(cQuery);

		List val = q.getResultList();
		return val;
	} finally {
		em.close();
	}
}


	/*
	 * 
	 * 
	 select reqshipfuelassdetail.id as [order], anayacht.id, anayacht.name,
	 reqshipfuelass.deliverydt, anayacht.captain, reqshipfuelass.porttoref,
	 anayacht.corporation, reqshipfuelassdetail.curr,
	 suppinvoicedetail.suppid, suppinvoicedetail.suppname, [order] & [suppid]
	 as [key], reqshipfuelass.art15 from ((captains right join (anayacht right
	 join reqshipfuelass on anayacht.id = reqshipfuelass.vesselnameid) on
	 captains.contactname = anayacht.captain) right join reqshipfuelassdetail
	 on reqshipfuelass.id = reqshipfuelassdetail.id) left join
	 suppinvoicedetail on (reqshipfuelassdetail.productid =
	 suppinvoicedetail.productid) and (reqshipfuelassdetail.id =
	 suppinvoicedetail.order) where (((reqshipfuelassdetail.stat)="CC" or
	 (reqshipfuelassdetail.stat)="SC" or (reqshipfuelassdetail.stat)="HI" or
	 (reqshipfuelassdetail.stat)="CL" or (reqshipfuelassdetail.stat)="IV"))
	 group by reqshipfuelassdetail.id, anayacht.id, anayacht.name,
	 reqshipfuelass.deliverydt, anayacht.captain, reqshipfuelass.porttoref,
	 anayacht.corporation, reqshipfuelassdetail.curr,
	 suppinvoicedetail.suppid, suppinvoicedetail.suppname,
	 reqshipfuelass.art15 having (((reqshipfuelassdetail.id)>1000)) order by
	 reqshipfuelassdetail.id;
	 
	 */
}
//
// fine
